package cn.remex.core.util.excelutils;

import cn.remex.RemexConstants;
import cn.remex.core.util.Arith;
import cn.remex.core.util.Judgment;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import cn.remex.core.util.excelutils.bean.ExcelRead;

import java.io.*;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;

/**
 * Created by guoqi on 2016/3/11.
 */
public class ExcelUtils {
//    public static void main(String[] args) {
//        File file = new File("C:\\Users\\guoqi\\Desktop\\test.xlsx");
//        ExcelImportService excelImportService = new ExcelImportService();
//        excelImportService.excelImport(file);
//
//    }

    public static String[][] getData(File file, int sheet, int startRow, Consumer<Double> consumer) {
        List<String[]> result = new ArrayList<>();
        int rowSize = 0;
        try {
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
            InputStream inp = new FileInputStream(file);
            Workbook wb = null;
            try {
                wb = new XSSFWorkbook(inp);
            } catch (Exception e) {
                wb = new HSSFWorkbook(inp);
            }
            Cell cell = null;
            Sheet st = wb.getSheetAt(sheet);
            int count =  st.getLastRowNum();
            int index = 0;
            for (int rowIndex = startRow; rowIndex <= st.getLastRowNum(); rowIndex++) {
                if(consumer!=null&&index%10000 == 0){
                    consumer.accept( Arith.div(index,count,2));
                }
                index++;
                Row row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                    cell = row.getCell(columnIndex);
                    String value = "";
                    if (!Judgment.nullOrBlank(cell)) {
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (date != null) {
                                        value = new SimpleDateFormat("yyyy-MM-dd hh:mm:dd")
                                                .format(date);
                                    } else {
                                        value = "";
                                    }
                                } else {
                                    value = new DecimalFormat("0").format(cell
                                            .getNumericCellValue());
                                }
                                break;
                        }
                        if (columnIndex == 0 && value.trim().equals("")) {
                            break;
                        }
                        values[columnIndex] = rightTrim(value);
                        hasValue = true;
                    }
                }
                if (hasValue) {
                    result.add(values);
                }
            }
            in.close();
        } catch (Exception e) {
            RemexConstants.logger.error("excel 表格解析错误！{}", e);
            throw new Error("表格解析错误！");
        }
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = result.get(i);
        }

        return rmBlack(returnArray);
    }

    public static ExcelRead getData(File file, int sheet, int tiltleRow, int dataStartRow,Consumer<Double> consumer) {
        ExcelRead excelRead = new ExcelRead();
        String[][] datas = getData(file,sheet,tiltleRow,consumer);
        String[] titles = datas[0];
        List<String[]> dataList = new ArrayList<>();
        dataList.addAll(Arrays.asList(datas).subList(1, datas.length));
        excelRead.setTitles(titles);
        excelRead.setList(dataList);
        return excelRead;
    }
    public static ExcelRead getData(File file, int sheet, int tiltleRow, int dataStartRow) {
        return getData(file,sheet,tiltleRow,dataStartRow,null);
    }

    /*
     * 去掉字符串右边的空格
     */
    public static String rightTrim(String str) {
        if (Judgment.nullOrBlank(str)) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }

    private static String[][] rmBlack(String[][] data) {
        List<String[]> list = new ArrayList<>();
        int rowSize = 0;
        for (String[] strings : data) {
            int black = 0;
            rowSize = strings.length;
            for (String string : strings) {
                if (Judgment.nullOrBlank(string)) {
                    black++;
                }
            }
            if (black == strings.length) {
                continue;
            }
            list.add(strings);
        }
        String[][] str = new String[list.size()][rowSize];
        for (int i = 0; i < str.length; i++) {
            str[i] = list.get(i);
        }
        return str;
    }


    private Workbook wb = null;
    private Sheet sheet = null;

    /*
     * 获取所读取excel模板的对象
     */
    public boolean getSheet(URL srcXlsPath, String sheetName) {
        try {
            if (Judgment.nullOrBlank(wb)) {
                try {
                    wb = new XSSFWorkbook(srcXlsPath.openStream());
                } catch (Exception e) {

                    wb = new HSSFWorkbook(srcXlsPath.openStream());
                }
            }
            sheet = wb.getSheet(sheetName);
            if (Judgment.nullOrBlank(sheet)) {
                sheet = wb.createSheet(sheetName);
            }
        } catch (IOException e) {
            RemexConstants.logger.error("四六级微信—>下载报名信息-获取模板：{}" , e.getMessage());
            e.printStackTrace();
        }
        return true;
    }

    public Row getRow(int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if (Judgment.nullOrBlank(row)) {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    public void setCellValue(int rowIndex, int cellnum, String value) {
        Cell cell = this.getRow(rowIndex).getCell(cellnum);
        if (Judgment.nullOrBlank(cell)) {
            cell = this.getRow(rowIndex).createCell(cellnum);
        }
        cell.setCellValue(value);
    }

    public void exportToNewFile(String outputFile) {
        FileOutputStream out = null;
        try {
            File f = new File(outputFile.substring(0, outputFile.lastIndexOf("/")));
            if (!f.exists() && !f.isDirectory()) {
                f.mkdirs();
            }
            out = new FileOutputStream(outputFile);
            wb.write(out);
        } catch (IOException e) {
            RemexConstants.logger.error("四六级微信—>下载报名信息-保存excel：{}" ,e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                if(out!=null)
                    out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
